package org.skipper.samples.melot;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.FileNameMap;
import java.nio.file.Files;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author shoujian
 * Date: 2022-12-16
 * Time: 14:11
 */
public class ActEpisode {

    public static final String EXCEL_XLS = ".xls";
    public static final String EXCEL_XLSX = ".xlsx";

    public static void main(String[] args) throws IOException{

        File file = new File("/Users/shoujian/Documents/00work/临时文件/database(1)");
        List<String> filelist = new ArrayList<>();
        List<String> list = getAllFiles(file, filelist);

        for (String s : list) {
//            System.out.println(s);
            List<Map<String, Object>> dataList = ActEpisode.read(s);

            String fileName = s.substring(s.lastIndexOf("/") + 1);

            File filepath = new File("/Users/shoujian/Documents/00work/临时文件/actsql/"+ fileName.substring(0,fileName.length()-5)+".sql");

            if (!filepath.getParentFile().exists()) {
                filepath.getParentFile().mkdirs();
            }

//            FileWriter fw = new FileWriter(filepath);
//            BufferedWriter  bw=new BufferedWriter(fw);
            for (Map<String, Object> stringObjectMap : dataList) {
                StringBuilder stringBuilder = new StringBuilder("insert into kkcx.act_episode(episode_id,act_id,episode_title,episode_number,starttime,endtime,poster,episode_url,dtime) values(nextval('kkcx.seq_act_episode')");
                if (!fileName.substring(0, fileName.length() - 5).contains((CharSequence) stringObjectMap.get("1"))) {
                    System.out.println(fileName.substring(0, fileName.length() - 5));
                    break;

                }
//                stringBuilder.append(",").append(stringObjectMap.get("1"));
//                stringBuilder.append(",'").append(stringObjectMap.get("2"));
//                stringBuilder.append("',").append(stringObjectMap.get("3"));
//                stringBuilder.append(",'").append(stringObjectMap.get("4"));
//                stringBuilder.append("', '").append(stringObjectMap.get("5"));
//                stringBuilder.append("', '").append(stringObjectMap.get("6"));
//                stringBuilder.append("','").append(stringObjectMap.get("7"));
//                stringBuilder.append("',now());");
//                bw.write(stringBuilder.toString()+ "\t\n");
            }

//            bw.close();
//            fw.close();



//            System.out.println(dataList);
        }



    }

    private static List<String> getAllFiles(File dir, List<String> filelist) {
        File[] fs = dir.listFiles();
        if (fs == null) {
            return filelist;
        }
        for (File f : fs) {
            //匹配log结尾的文件
            //若为文件夹，就调用getAllFiles方法
            if (f.isDirectory()) {
                try {
                    getAllFiles(f, filelist);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else {
                filelist.add(f.getAbsolutePath());
            }
        }
        return filelist;
    }
    public static List<Map<String, Object>> read(String path) {
        File file = new File(path);
        Workbook workbook = readWorkbook(file);
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(0);
        // 获取总行数
        int rows = sheet.getLastRowNum() + 1;
        // 获取所有列名

        Row columnNameRow = sheet.getRow(0);
        String[] columnNameArr = new String[columnNameRow.getLastCellNum()];
        for (int i = 0; i < columnNameArr.length; i++) {
            columnNameArr[i] =  i + "";
        }

        // 存储读取到的excel数据
        List<Map<String, Object>> list = new ArrayList<>();

        Map<String, Object> data;
        Row row;
        // 遍历Excel每行
        for (int i = 0; i < rows; i++) {
            row = sheet.getRow(i);
            // 判断空行
            if (isRowEmpty(row)) {
                continue;
            }

            try {
                data = new HashMap<>();

                for (int j = 0; j < columnNameArr.length; j++) {
                    data.put(columnNameArr[j], getCellValue(row.getCell(j)));
                }
                list.add(data);
            } catch (Exception e) {
//                LOGGER.error("读取Excel文件失败", e);
            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
//            LOGGER.error("关闭workbook失败", e);
        }

        return list;
    }

    /**
     * 获取Excel单元格内容
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 把数字当成String来读，避免出现1读成1.0的情况
    /*if(cell.getCellType() == CellType.NUMERIC){
        cell.setCellType(CellType.STRING);
    }*/
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                SimpleDateFormat sdf = null;
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 处理日期格式、时间格式
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else { // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(value);
                    cellValue = sdf.format(date);
                } else {
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    /**
     * 检验是否空行
     */
    private static boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    private static Workbook readWorkbook(File file) {
        Workbook workbook = null;
        try (FileInputStream inputStream = new FileInputStream(file)) {
            checkExcelValid(file);
            if (file.getName().endsWith(EXCEL_XLS)) { // Excel 2003
                workbook = new HSSFWorkbook(inputStream);
            } else if (file.getName().endsWith(EXCEL_XLSX)) { // Excel 2007/2010
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
//            LOGGER.error("读取Workbook异常", e);
        }
        return workbook;
    }

    /**
     * 校验Excel文件
     */
    private static void checkExcelValid(File file) {
        if (file == null || !Files.exists(file.toPath())) {
            throw new RuntimeException("文件不存在");
        }
        if (!((file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            throw new RuntimeException("不是Excel文件");
        }
    }

}
